﻿using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

/// <summary>
///MySQLHelper 的摘要说明
/// </summary>
public class MySQLHelper
{
    //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.		
    public static string connectionString = ConfigurationManager.ConnectionStrings["perf_confConnectionString"].ToString();

    public MySQLHelper()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    #region ExecuteNonQuery

    public static int ExecuteNonQuery(CommandType commandType, string commandText)
    {
        return ExecuteNonQuery(commandType, commandText, (MySqlParameter[])null);
    }
    public static int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            int result = 0;
            try
            {
                // if (connection == null) throw new ArgumentNullException("connection");
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (commandParameters != null)
                    cmd.Parameters.AddRange(commandParameters);
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }
    }

    #endregion


    #region ExecuteDataSet
    /// <summary>
    /// 执行查询
    /// </summary>
    /// <param name="conn">数据库连接字符串</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="commandText">命令文本</param>
    /// <returns></returns>
    public static DataSet ExecuteDataset(string conn, CommandType commandType, string commandText)
    {
        return ExecuteDataset(conn, commandType, commandText, (MySqlParameter[])null);
    }
    /// <summary>
    /// 执行查询命令
    /// </summary>
    /// <param name="conn">数据库连接字符串</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="commandText">命令文本</param>
    /// <param name="commandParameters">命令参数</param>
    /// <returns></returns>
    public static DataSet ExecuteDataset(string conn, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        connectionString = ConfigurationManager.ConnectionStrings[conn].ToString();
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (commandParameters != null)
                    cmd.Parameters.AddRange(commandParameters);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                cmd.Dispose();
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return ds;
        }
    }
    /// <summary>
    /// 执行查询命令
    /// </summary>
    /// <param name="commandType">命令类型</param>
    /// <param name="commandText">命令文本</param>
    /// <param name="commandParameters">命令参数</param>
    /// <returns></returns>
    public static DataSet ExecuteDataset(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandTimeout = 30;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (commandParameters != null)
                    cmd.Parameters.AddRange(commandParameters);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                cmd.Dispose();
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return ds;
        }
    }
    #endregion

    #region ExecuteDataReader

    public static MySqlDataReader ExecuteReader(CommandType commandType, string commandText)
    {
        return ExecuteReader(commandType, commandText, (MySqlParameter[])null);
    }
    public static MySqlDataReader ExecuteReader(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            MySqlDataReader msd = null;
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (commandParameters != null)
                    cmd.Parameters.AddRange(commandParameters);
                msd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return msd;
        }
    }
    #endregion

    #region ExecuteScalar

    /// <summary>
    /// Execute a single command against a MySQL database.
    /// </summary>
    /// <param name="connectionString">Settings to use for the update</param>
    /// <param name="commandText">Command text to use for the update</param>
    /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
    public static object ExecuteScalar(string commandText)
    {
        //pass through the call providing null for the set of MySqlParameters
        return ExecuteScalar(commandText, (MySqlParameter[])null);
    }

    /// <summary>
    /// Execute a single command against a MySQL database.
    /// </summary>
    /// <param name="connectionString">Settings to use for the command</param>
    /// <param name="commandText">Command text to use for the command</param>
    /// <param name="commandParameters">Parameters to use for the command</param>
    /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
    //public static object ExecuteScalar(string commandText, params MySqlParameter[] commandParameters)
    //{
    //    //create & open a SqlConnection, and dispose of it after we are done.
    //    using (MySqlConnection connection = new MySqlConnection(connectionString))
    //    {
    //        connection.Open();

    //        //call the overload that takes a connection in place of the connection string
    //        return ExecuteScalar(commandText, commandParameters);
    //    }
    //}

    /// <summary>
    /// Execute a single command against a MySQL database.
    /// </summary>
    /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
    /// <param name="commandText">Command text to use for the command</param>
    /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
    //public static object ExecuteScalar(string commandText)
    //{
    //    //pass through the call providing null for the set of MySqlParameters
    //    return ExecuteScalar(commandText, (MySqlParameter[])null);
    //}

    /// <summary>
    /// Execute a single command against a MySQL database.
    /// </summary>
    /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
    /// <param name="commandText">Command text to use for the command</param>
    /// <param name="commandParameters">Parameters to use for the command</param>
    /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
    public static object ExecuteScalar(string commandText, params MySqlParameter[] commandParameters)
    {
        //create a command and prepare it for execution
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            object retval = new object();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;

                if (commandParameters != null)
                    foreach (MySqlParameter p in commandParameters)
                        cmd.Parameters.Add(p);
                //execute the command & return the results
                // detach the SqlParameters from the command object, so they can be used again.
                cmd.Parameters.Clear();
                retval = cmd.ExecuteScalar();
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return retval;
        }
    }

    #endregion

    #region 数据集操作
    //此方法将所选字段的唯一值复制到一个新的 DataTable 。 如果字段包含 NULL 值，目标表中的记录还包含 NULL 值 
    public static DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
    {
        DataTable dt = new DataTable(TableName);
        dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

        object LastValue = null;
        foreach (DataRow dr in SourceTable.Select("", FieldName))
        {
            if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
            {
                LastValue = dr[FieldName];
                dt.Rows.Add(new object[] { LastValue });
            }
        }
        ////if (ds != null)
        ////ds.Tables.Add(dt);
        return dt;
    }
    //此方法将所选字段的唯一值复制到一个新的 DataTable 。 如果字段包含 NULL 值，目标表中的记录还包含 NULL 值 
    public static DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName,string FieldValue)
    {
        DataTable dt = new DataTable(TableName);
        dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

        object LastValue = null;
        foreach (DataRow dr in SourceTable.Select("", FieldName))
        {
            if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
            {
                LastValue = dr[FieldName];
                if (LastValue.ToString() == FieldValue) {
                    dt.Rows.Add(new object[] { LastValue });
                }
            }
        }
        ////if (ds != null)
        ////ds.Tables.Add(dt);
        return dt;
    }
    static bool ColumnEqual(object A, object B)
    {
        if (A == DBNull.Value && B == DBNull.Value)   //   both   are   DBNull.Value   
            return true;
        if (A == DBNull.Value || B == DBNull.Value)   //   only   one   is   DBNull.Value   
            return false;
        return (A.Equals(B));   //   value   type   standard   comparison   
    }

    #endregion


    #region 公共操作
    /// <summary>
    /// 调用存储过程获取统计数据
    /// </summary>
    /// <param name="conn">数据库连接字符串</param>
    /// <param name="procedureName">存储过程名称</param>
    /// <param name="equipID">设备ID</param>
    /// <param name="sTime">开始事件</param>
    /// <param name="eTime">结束时间</param>
    /// <param name="pIsTotal">统计类型</param>
    /// <returns></returns>
    public static DataSet getDataSet(string conn, string procedureName, string equipID, string sTime, string eTime,string pIsTotal)
    {
        CommandType commtype = CommandType.StoredProcedure;
        //设置存储过程参数
        MySqlParameter[] mySqlPar = new MySqlParameter[4];
        mySqlPar[0] = new MySqlParameter("p_ne_id", MySqlDbType.VarChar);
        mySqlPar[0].Value = equipID;
        mySqlPar[1] = new MySqlParameter("p_stime", MySqlDbType.DateTime);
        mySqlPar[1].Value = Convert.ToDateTime(sTime);
        mySqlPar[2] = new MySqlParameter("p_etime", MySqlDbType.DateTime);
        mySqlPar[2].Value = Convert.ToDateTime(eTime);
        mySqlPar[3] = new MySqlParameter("p_is_total", MySqlDbType.VarChar);
        mySqlPar[3].Value = pIsTotal;
        //调用函数执行存储过程
        DataSet ds = MySQLHelper.ExecuteDataset(conn, commtype, procedureName, mySqlPar);
        return ds;
    }
    /// <summary>
    /// 调用存储过程获取统计数据
    /// </summary>
    /// <param name="conn">数据库连接字符串</param>
    /// <param name="procedureName">存储过程名称</param>
    /// <param name="count">记录前数目</param>
    /// <param name="sTime">开始事件</param>
    /// <param name="eTime">结束时间</param>
    /// <returns></returns>
    public static DataSet getDataSetTop(string conn, string procedureName, string count, string sTime, string eTime)
    {
        CommandType commtype = CommandType.StoredProcedure;
        //设置存储过程参数
        MySqlParameter[] mySqlPar = new MySqlParameter[3];
        mySqlPar[0] = new MySqlParameter("p_TopCount", MySqlDbType.Int32);
        mySqlPar[0].Value = Convert.ToInt32(count);
        mySqlPar[1] = new MySqlParameter("p_stime", MySqlDbType.DateTime);
        mySqlPar[1].Value = Convert.ToDateTime(sTime);
        mySqlPar[2] = new MySqlParameter("p_etime", MySqlDbType.DateTime);
        mySqlPar[2].Value = Convert.ToDateTime(eTime);
        //调用函数执行存储过程
        DataSet ds = MySQLHelper.ExecuteDataset(conn, commtype, procedureName, mySqlPar);
        return ds;
    }
    /// <summary>
    /// 转换数据，将数据集列行转换
    /// </summary>
    /// <param name="conn">数据库连接字符串</param>
    /// <param name="dt">原数据集</param>
    /// <param name="colName">字段名称</param>
    /// <param name="reportid">报表类型ID</param>
    /// <returns></returns>
    public static DataTable ConvertTable(string conn, DataTable dt, string colName, string reportid)
    {
        DataTable dtNew = GetTable(colName);
        try
        {
            int rCount = dt.Rows.Count;//行数
            // int cCount = dt.Columns.Count;//列数           

            DataSet ds = GetIndicatorsData(conn, Convert.ToInt32(reportid));
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                int length = ds.Tables[0].Rows.Count;
                for (int i = 0; i < length; i++)
                {
                    for (int k = 0; k < rCount; k++)
                    {
                        DataRow dr = dtNew.NewRow();
                        dr[0] = dt.Rows[k][colName].ToString();
                        dr[1] = ds.Tables[0].Rows[i][2].ToString();
                        dr[2] = ds.Tables[0].Rows[i][3].ToString();
                        dr[3] = dt.Rows[k][ds.Tables[0].Rows[i][3].ToString()].ToString();
                        dtNew.Rows.Add(dr);
                    }
                }
            }
        }
        catch (Exception e)
        {

        }
        return dtNew;
    }
    /// <summary>
    /// 创建转换表
    /// </summary>
    /// <param name="colName">字段名称</param>
    /// <returns></returns>
    public static DataTable GetTable(string colName)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add(colName);
        dt.Columns.Add("IndicatorsCN");
        dt.Columns.Add("Indicators");
        dt.Columns.Add("IndicatorsValue");
        return dt;
    }
    /// <summary>
    /// 获取指标数据集
    /// </summary>
    /// <param name="conn">数据库连接字符串</param>
    /// <param name="reportid">报表类型ID</param>
    public static DataSet GetIndicatorsData(string conn, int reportid)
    {
        CommandType commtype = CommandType.StoredProcedure;
        //存储过程参数
        MySqlParameter[] mySqlPar = new MySqlParameter[1];
        mySqlPar[0] = new MySqlParameter("reporttype", MySqlDbType.Int32);
        mySqlPar[0].Value = reportid;
        //调用函数执行存储过程
        return MySQLHelper.ExecuteDataset(conn, commtype, "report_get_map", mySqlPar);
    }
    #endregion
}
